#!/usr/bin/env python
# encoding: utf-8
#################
# Name:         一点接口用户数据，清洗 
# Purpose:
#
# Author:      stephen
#
# Created:     02/20/2019
# Licence:     1.0.0
#
#################
import logging
import ConfigParser
import re, os, random, sys, string, hashlib, math, uuid
import calendar
import json,struct
import numpy as np
import pymysql

#from datetime import *
import datetime
import time
from LOCAL_CONFIG import *
from phone   import Phone
from birth   import Birth
from pyimei  import ImeiSupport
from func    import  *  

reload(sys)
sys.setdefaultencoding('utf-8')
#sys.path.append(os.environ['HOME']+"/bin")
sys.path.append(os.environ['HOME']+"/lib")  
from pyctlib import *

VERSION_NUM  = '1.0.0'
VERSION_DATE = '2019-02-20'

class UniDB:
  def __init__(self,user,passwd,server,port,dbname,dbtype="Oracle"):
    self.__server = server
    self.__port   = port
    self.__dbname = dbname
    self.__user   = user
    self.__passwd = passwd
    self.__dbtype = dbtype.lower()
    self.dbconn   = None
  def __del__(self):
    pass
  def open(self):
    if self.__dbtype == "oracle":
      os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import cx_Oracle
      dsn_tns = cx_Oracle.makedsn(self.__server, self.__port  , self.__dbname)
      self.dbconn=cx_Oracle.connect(self.__user, self.__passwd, dsn_tns)
    elif self.__dbtype == "mysql":
      #os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import pymysql
      self.dbconn=pymysql.connect(host  =self.__server, port=self.__port, user=self.__user,\
                                  passwd=self.__passwd, db  =self.__dbname, charset='utf8')
    else:
      raise TypeError("OnlyOracle.")
    logging.info("DB[%s] Open."%self.__dbtype)
  def close(self):
    try:
      if self.dbconn:
        self.dbconn.close()
      self.dbconn=None
      logging.info("DB[%s]Closed."%self.__dbtype)
    except Exception,e:
      logging.info("DB[%s]ClosedExcept[%s]."%(self.__dbtype,e))
      self.dbconn=None
      
class UniCfg:
  def __init__(self,cfgfile):
    self.cfgfile=cfgfile
    self.load_cfg()
  def load_cfg(self):
    cf = ConfigParser.ConfigParser()
    cf.read(self.cfgfile)
    
    self.dbhost = cf.get("db"   , "dbhost")
    self.dbport = cf.getint("db", "dbport")
    self.dbname = cf.get("db"   , "dbname")
    self.dbuser = cf.get("db"   , "dbuser")
    self.dbpass = cf.get("db"   , "dbpass")
    self.dbtype = cf.get("db"   , "dbtype")
    
    self.logfile = cf.get("log","logfile")
    self.loglevel= cf.get("log","loglevel").upper()

class Handle:
  def __init__(self,udb=None,cfg=None):
    self.dbconn  = udb.dbconn
    self.cfg  = cfg
    self.__user_id     = 1
    self.__table       = 'usr_user_basic_info'
    self.__init_info() 
    self.__synonym_key = ['user_sex', 'cur_age', 'constellation','blood_type',\
         'profession', 'education_degree', 'cet46', 'residential_province', \
         'residential_city', 'residential_district', 'idcard_province',\
          'idcard_district']
     
  def __init_info(self):
    #sql = "select * from usr_user_basic_info where imei='355819409249602'"
    #sql = " select imei from usr_user_basic_info where imei is not null \
    #                order by imei asc  limit 1"
    #self.__users = self.__select_db(sql, True) 

    #sql = "select * from yd_client_device where imei='868753020045782'" 
    sql = "select * from yd_client_device where imei is not null" 
    self.__imei = self.__select_db(sql, True)                              

    sql = "select tag_name, synonym from tg_tags_info where class_id=29"
    self.__province  = self.__select_db(sql, True)
    sql = "select tag_name, synonym from tg_tags_info where class_id=30"
    self.__city  = self.__select_db(sql, True)
    sql = "select tag_name, synonym from tg_tags_info where class_id=31"
    self.__district  = self.__select_db(sql, True)
    sql = "select tag_name, synonym  from tg_tags_info where class_id=32"
    self.__community  = self.__select_db(sql, True)
    sql = "select province, city from yd_province_city_info" 
    self.__province_city = self.__select_db(sql, True)
     
 
  def handle_data (self):
    try:
      logging.info('program init finish!')
      self.__fetch_data()
    except  Exception,err:                                                   
      import traceback                                                       
      import StringIO                                                        
      fp = StringIO.StringIO()                                               
      traceback.print_exc(file=fp)                                           
      message = fp.getvalue()                                                
      logging.info(message)                                                  
                                                                             
      logging.warning('err:%s %s' %(err, sys._getframe().f_back.f_code) ) 


  def __fetch_data(self):
    index = 0 
    cursor = self.dbconn.cursor()
    for re_imei in self.__imei:                                            
      logging.info( re_imei['imei'])                                     
      sql = self.__handle_data( re_imei)                                 
      if sql:                                                            
        logging.info("index:%d sql:%s"%(index, sql)) 
        #self.__update_db(sql)
        cursor.execute(sql)
        if index%500 == 0:
          logging.info('index %d db commit'%(index))
          self.dbconn.commit()
        index += 1
    logging.info('end index %d db commit'%(index))                           
    self.dbconn.commit()                                                     
    cursor.close()  
     
  def __fetch_data_1(self):
    index = 0
    for re_user in self.__users:
      for re_imei in self.__imei:
        if re_user.get('imei') == re_imei.get('imei'): 
          logging.info( re_imei['imei'])
          sql = self.__handle_data( re_imei)
          if sql:
            logging.info("index:%d sql:%s"%(index, sql))
            index +=1
          self.__imei.remove(re_imei)
          if index == 1000:
            return
    print( self.__imei)


  def __handle_data(self, re_imei):
    if  type(re_imei) != dict:
      return 
    if  len(re_imei) < 1:
      return
    add_dict = {}
   
    #手机型号
    if re_imei.get('device_model'):
      add_dict['cellphone_model'] = re_imei.get('device_model') 
    #系统类型
    if re_imei.get('device_type'):
       dev_type =  str(re_imei.get('device_type')).strip()
       if dev_type == '1' or   dev_type =='a':                            
         add_dict['cellphone_os_type'] = '安卓' 
       elif dev_type == '2' or  dev_type == 'i' or  dev_type =='ios':     
         add_dict['cellphone_os_type'] = 'IOS'
    #系统版本 
    if re_imei.get('device_platform'):
      add_dict['cellphone_os_version'] = re_imei.get('device_platform')

    if re_imei.get('device_model'):
      add_dict['cellphone_model'] = re_imei.get('device_model')
   
    #handle the city 
    if re_imei.get('city'): 
      self.__handle_city(  re_imei, add_dict ) 

    #handel the address
    if re_imei.get('address'):
      self.__handle_address(re_imei, add_dict)
 
    if len(add_dict )>0:
      add_dict['imei'] = re_imei.get('imei')  
      sql = generate_update_sql( add_dict, 'usr_user_basic_info')
      return sql 


  #re  yd_client_device 符合条件的字典
  def __handle_city(self, re, add_dict):
    if type(re) != dict or len(re)<1:
      return
    if not re.get('city'):
      return
    
    ge_city = self.__get_tag_name(self.__city, re.get('city'))
    
    if not ge_city:
      return 
    ge_province=None 
    for re in self.__province_city:   
      if not re.get('city') or not re.get('province'):
        continue
      if re.get('city').find(ge_city)  != -1:                             
        ge_province = re.get('province')
    if ge_city and ge_province:
      add_dict['residential_city']     = ge_city
      add_dict['residential_province'] = ge_province


  def __handle_address(self, re, add_dict):
    # to find the city in tg_tags_info                                     
    if type(re) != dict or len(re)<1:
      return
    if not re.get('address'):
      return 

    district = self.__get_tag_name( self.__district, re.get('address'), False )
    if district:
      add_dict['residential_district'] = district
    community  = self.__get_tag_name( self.__community, re.get('address'), False)
    if community:
      add_dict['residential_community'] = community 
  
  

  # res 的标签数据，， 返回val的tagname
  def __get_tag_name(self,  res, val, equals=True):
    if type(res) != list or len(res) <1:                                     
      return                                                                 
    val = str(val).decode('utf-8').strip()                                   
                                                                             
    for re in res:                                                           
      if not  re.get('tag_name'):                                            
        continue    
      if equals:                                                         
        #if hash(re.get('tag_name').strip()) == hash(val):                                  
        if re.get('tag_name').strip() == val:
          return re.get('tag_name')
      else:
        if val.find( re.get('tag_name'))  != -1:
          return re.get('tag_name')
 
      if re.get('synonym') and re.get('synonym').find(',') != -1:            
        list_value = re.get('synonym').split(',')                            
        if len(list_value) <1:                                               
          continue                                                           
        for value in list_value: 
          if equals:
            #if hash(value.strip()) == hash(val):                                           
            if value.strip() == val:
              return re.get('tag_name')                                        
          else:
            if  val.find( re.get('tag_name'))  != -1:
              return re.get('tag_name')  
 
      if re.get('synonym') and re.get('synonym').find(',') == -1:            
        if equals:
          #if hash(re.get('synonym').strip()) == hash(val):                                 
          if re.get('synonym').strip() == val:
            return re.get('tag_name')  
        else:
          if  val.find( re.get('tag_name'))  != -1:
            return re.get('tag_name')  
                                                                           
     
	
  #通过select count 查询数据
  def __table_cnt_count(self,  table=None, wh=None ):
    if table == None:
      raise TypeError('tale name is errro')
    sql = "select count(1) from %s "%(table)
    if wh:
      sql +=  'where %s'%( wh)                                               
    
    res = self.__select_db(sql )
    #print( res )                                                            
    return res[0][0]  

  

  def __update_db(self, sql):
    try:
      cursor = self.dbconn.cursor()
      cursor.execute(sql)
      #logging.info(sql)
      #self.dbconn.commit()
      cursor.close()
    except Exception,err:
      logging.warning('err:%s' %(err) )

  def __select_db(self, sql, dictionary=None):
    try:
      if dictionary:
        cursor = self.dbconn.cursor( pymysql.cursors.DictCursor )
      else:
        cursor = self.dbconn.cursor()                                        
     
      self.dbconn.commit()
      cursor.execute(sql )                                                    
      res = cursor.fetchall()                                                
      cursor.close()                                                         
      return res                                                             
    except Exception,err:                                                    
      logging.warning('err:%s' %(err) )  


def main():
   if len(sys.argv)<>3 or sys.argv[1]<>"-f":
     print "Run Like: %s -f %s.conf"%(sys.argv[0][:-3],sys.argv[0][:-3])
     exit(1)
   cfg_file = None
   if sys.argv[2].find('/')>=0:
     cfg_file=sys.argv[2].find('/')
   else:
     cfg_file=os.environ['HOME']+"/cfg/"+sys.argv[2]
   cfg = UniCfg(cfg_file)
   
   if cfg.loglevel == "DEBUG":
     iLogLevel = logging.DEBUG
   elif cfg.loglevel == "INFO":
     iLogLevel = logging.INFO
   elif cfg.loglevel == "WARN" or cfg.loglevel == "WARNING":
     iLogLevel = logging.WARNING
   else:
     iLogLevel = logging.ERROR
   logging.basicConfig( filename=cfg.logfile,
                        format  = "%(asctime)s %(levelname)s[%(process)d]:%(message)s",
                        level   = iLogLevel)
   
   logging.info("......START.........")
   UDB = UniDB(cfg.dbuser,cfg.dbpass,cfg.dbhost,cfg.dbport,cfg.dbname,cfg.dbtype)
   UDB.open()
   
   obj = Handle(UDB,cfg)
   obj.handle_data()
   
   UDB.close()
   logging.info("......EXIT.........")
   logging.shutdown()
   return

if __name__ == "__main__":
  main()
